package com.ibpd.henuocms.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
      
    public class SQLDBUtil {  
    	 private static Connection con = null;  
    	 public static Connection getCon() {
 			return con;
 		}
    	 public static void CloseCon() throws SQLException {
    		con.close(); 
     		con=null;
 		}
		public static void initConnection(){
             try {
            	 String connectionUrl = "jdbc:sqlserver://192.105.128.1:1433;DatabaseName=jjlzb";  
            	 String url = connectionUrl;
 				 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
	             if(con==null || con.isClosed())
	             	con = DriverManager.getConnection(url,"sa","jjlserver");  
	 		} catch (SQLException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
 		}
    	 public static void closeConnection(){
           if (con != null)  
	           try {  
	               con.close();  
	           } catch (Exception e) {  
	        	   e.printStackTrace();
	           }  
    	 }
    	 public static String getBaseBarCode(String goodsCode){
    	   		String rtn="";
                Statement stmt = null;  
                ResultSet rs = null;  
                try {   
                    String SQL = "SELECT [GoodsCode],[BaseBarCode] FROM [jjlzb].[000].[tbGoods] where GoodsCode='"+goodsCode+"'";  
                    stmt = con.createStatement();  
                    rs = stmt.executeQuery(SQL);  
            
                    while (rs.next()) {  
                        System.out.println(rs.getString(1) + " " + rs.getString(2));  
                        rtn=rs.getString(2);
                    }  
                }
                catch (Exception e) {  
                    e.printStackTrace();  
                }
                finally {
                    if (rs != null)  
                        try {
                            rs.close();  
                        } catch (Exception e) {
                        	e.printStackTrace();
                        }  
                    if (stmt != null)  
                        try {
                            stmt.close();  
                        } catch (Exception e) {
                        	e.printStackTrace();
                        }  
                }  
                return rtn;
    	 }
    	 public static List<Map<String,String>> getDistPrice(String sql){
     		List<Map<String,String>> rtn=new ArrayList<Map<String,String>>();
            Statement stmt = null;  
            ResultSet rs = null;  
            initConnection();
            try {   
                String SQL = sql;  
                stmt = con.createStatement();  
                rs = stmt.executeQuery(SQL);  
        
                while (rs.next()) {  
                	Map<String,String> m=new HashMap<String,String>();
                	ResultSetMetaData rsmd=rs.getMetaData();
                	for(Integer i=0;i<rsmd.getColumnCount();i++){
                		String key=rsmd.getColumnName(i+1);
                		m.put(key, rs.getObject(key).toString());
                	}
                	rtn.add(m);
                }  
            }  
      
            catch (Exception e) {  
                e.printStackTrace();  
            }  
      
            finally {  
                if (rs != null)  
                    try {  
                        rs.close();  
                    } catch (Exception e) {  
                    }  
                if (stmt != null)  
                    try {  
                        stmt.close();  
                    } catch (Exception e) {  
                    }  
                if (con != null)  
                    try {  
                        con.close();  
                    } catch (Exception e) {  
                    }  
            }  
            return rtn;    		 
    	 }
    	public static String getContraceNumber(String untiCode,String goodsCode){
    		String rtn="";
           
            Statement stmt = null;  
            ResultSet rs = null;  
      
            try {   
                String SQL="SELECT top(1) con.ContractNumber,con.SupplierCode,con.EndDate FROM [jjlzb].[000].[tbContract] con,[000].tbDeptGoodsSupp good where con.ContractState=1 and con.SupplierCode="+untiCode+" and good.SupplierCode='"+untiCode+"' and good.GoodsCode='"+goodsCode+"' and con.ContractNumber=good.ContractNumber order by EndDate desc";
                stmt = con.createStatement();  
                rs = stmt.executeQuery(SQL);  
        
                while (rs.next()) {  
                    System.out.println(rs.getString(1) + " " + rs.getString(2));  
                    rtn=rs.getString(1);
                }  
            }  
      
            catch (Exception e) {  
                e.printStackTrace();  
            }  
      
            finally {  
                if (rs != null)  
                    try {  
                        rs.close();  
                    } catch (Exception e) {  
                    }  
                if (stmt != null)  
                    try {  
                        stmt.close();  
                    } catch (Exception e) {  
                    }  
//                if (con != null)  
//                    try {  
//                        con.close();  
//                    } catch (Exception e) {  
//                    }  
            }  
            return rtn;
    	}
    	
//        public static void main(String args[]) {  
//            // Create a variable for the connection string.  
//            String connectionUrl = "jdbc:sqlserver://192.105.128.1:1433;DatabaseName=jjlzb";  
//      
//            String url = connectionUrl;//"jdbc:sqlserver://127.0.0.1:1368;databaseName=mydb;user=sa;password=qiaoning";//sa身份连接  
//           
//            // Declare the JDBC objects.  
//            Connection con = null;  
//            Statement stmt = null;  
//            ResultSet rs = null;  
//      
//            try {  
//                // Establish the connection.  
//                System.out.println("begin.");  
//                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  
//                con = DriverManager.getConnection(url,"sa","jjlserver");  
//                System.out.println("end.");  
//      
//                // Create and execute an SQL statement that returns some data.  
//                String SQL = "SELECT top(1) ContractNumber,SupplierCode,EndDate  FROM [jjlzb].[000].[tbContract] where ContractState=1 and SupplierCode=120054 order by EndDate desc";  
//                stmt = con.createStatement();  
//                rs = stmt.executeQuery(SQL);  
//      
//                // Iterate through the data in the result set and display it.  
//                while (rs.next()) {  
//                    System.out.println(rs.getString(1) + " " + rs.getString(2));  
//                }  
//            }  
//      
//            // Handle any errors that may have occurred.  
//            catch (Exception e) {  
//                e.printStackTrace();  
//            }  
//      
//            finally {  
//                if (rs != null)  
//                    try {  
//                        rs.close();  
//                    } catch (Exception e) {  
//                    }  
//                if (stmt != null)  
//                    try {  
//                        stmt.close();  
//                    } catch (Exception e) {  
//                    }  
//                if (con != null)  
//                    try {  
//                        con.close();  
//                    } catch (Exception e) {  
//                    }  
//            }  
//        }  
    }  